﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffIdInRoleToViewClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffIdInRoleToViewClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffIdInRoleToViewClass]  
    @ClassID int ,
    @Act_ID int
/*

============================================================
功能:    得到角色中能对@ClassID进行@Act_ID操作的所有成员
参数:
    @ClassID int         :    组（类）ID
    @Act_ID int        :    浏览权ID

============================================================

*/

AS
declare @team_ID int
declare @IDS nvarchar(255)
declare @team_IDS nvarchar(255)
declare @Sql nvarchar(2000)


--Set @ClassID = 1
Set @IDS =''
Set @team_IDS =''
Set @Sql=N'select staff_ID from uds_staff where 1=2'

--得到拥有此权限所在角色的ID
Select @team_IDS= @team_IDS + convert(nvarchar,role_ID) + ',' 
    From UDS_Assign_Rule a,uds_class b 
    Where b.classid = a.team_id and b.classid =@ClassID and based_on =3 and a.Act_ID = @Act_id

if  Len(@team_IDS)>0        
Begin
    Set @IDS = substring(@team_IDS,1,len(@team_IDS)-1)
    If len(@ids)>0    
        Set @Sql =@sql + N' union select staff_ID from uds_staff_in_role where Role_ID in (' + @ids  + ')'
    
        print @sql
End

Else
    Print 'no record'
Execute sp_executesql @sql